﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using YunHan.BuShi123.Model;
using DotNet.Utilities;
using DotNet.Extention;

namespace YunHan.BuShi123.DAL.Base
{
	/// <summary>
	/// Info的数据访问操作类
	/// </summary>
	public class Info_Base_DAL
	{
		/// <summary>
		/// 添加Info
		/// </summary>
		/// <param name="info">Info</param>
        /// <param name="trans">事务</param>
		/// <returns>最新编号</returns>
		public static int Add(Info_Model info, SqlTransaction trans)
		{
			string sql = "INSERT INTO Info(title,context,sort) VALUES (@title,@context,@sort);SELECT @@IDENTITY";
			
            SqlParameter[] parms = {	
                    new SqlParameter("@title", info.title),    
                    new SqlParameter("@context", info.context),    
                    new SqlParameter("@sort", info.sort),    
			};
			
            if (trans == null)
                return BuShi123SqlHelper.ExecuteScalar(sql, parms).ToInt();
            else
                return BuShi123SqlHelper.ExecuteScalar(trans, sql, parms).ToInt();
		}
        
        /// <summary>
		/// 添加Info
		/// </summary>
		/// <param name="info">Info</param>
		/// <returns>最新编号</returns>
		public static int Add(Info_Model info)
		{
			return Add(info, null);
		}
        
        
        /// <summary>
        /// 批量添加Info
        /// </summary>
        /// <param name="ModelList"></param>
        /// <param name="BatchSize">每一批次的行数，每一批次结束时，将将批次的发送到服务器</param>
        /// <returns>最新编号</returns>
        public static void BatchAdd(List<Info_Model> ModelList, int BatchSize)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("ID");
            dt.Columns.Add("title");
            dt.Columns.Add("context");
            dt.Columns.Add("sort");
            
            foreach (var item in ModelList)
            {
                DataRow dr = dt.NewRow();
                
					dr["ID"] = 
                        item.ID
                    ;
					dr["title"] = 
                        item.title == null ? "" : item.title
                    ;
					dr["context"] = 
                        item.context == null ? "" : item.context
                    ;
					dr["sort"] = 
                        item.sort
                    ;

                dt.Rows.Add(dr);
            }

            System.Data.SqlClient.SqlBulkCopy Bulk = new System.Data.SqlClient.SqlBulkCopy(BuShi123SqlHelper.ConnectionString);
            Bulk.DestinationTableName = "Info";
            Bulk.BatchSize = BatchSize;
            Bulk.WriteToServer(dt);
        }
		
		/// <summary>
		/// 批量添加Info
		/// </summary>
        /// <param name="dt"></param>
        /// <param name="BatchSize">每一批次的行数，每一批次结束时，将将批次的发送到服务器</param>
		/// <returns>最新编号</returns>
        public static void BatchAdd(DataTable dt, int BatchSize)
        {
            System.Data.SqlClient.SqlBulkCopy Bulk = new System.Data.SqlClient.SqlBulkCopy(BuShi123SqlHelper.ConnectionString);
            Bulk.DestinationTableName = "Info";
            Bulk.BatchSize = BatchSize;
            Bulk.WriteToServer(dt);
        }
        
		/// <summary>
        /// 更新一条数据
        /// </summary>
        /// <remarks>
        ///  e.g.:  
        ///  int result = Update("Name = @Name", "ID = @ID", new SqlParameter[]{new SqlParameter("@ID", 24), new SqlParameter("@Name", "test")});
        /// </remarks>
        /// <param name="updateSql">更新内容(不含SET)</param>
        /// <param name="whereSql">更新条件（不含WHERE）</param>
        /// <param name="parms">更新参数</param>
        /// <returns></returns>
        public static int Update(SqlTransaction trans, string updateSql, string whereSql, params SqlParameter[] parms)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("UPDATE [Info] SET ");
            sql.Append(updateSql);
            sql.Append(" WHERE ");
            sql.Append(whereSql);

            if (trans == null)
                return BuShi123SqlHelper.ExecuteNonQuery(sql.ToString(), parms);
            else
                return BuShi123SqlHelper.ExecuteNonQuery(trans, sql.ToString(), parms);
        }
        
        /// <summary>
        /// 更新一条数据
        /// </summary>
        /// <remarks>
        ///  e.g.:  
        ///  int result = Update("Name = @Name", "ID = @ID", new SqlParameter[]{new SqlParameter("@ID", 24), new SqlParameter("@Name", "test")});
        /// </remarks>
        /// <param name="updateSql">更新内容(不含SET)</param>
        /// <param name="whereSql">更新条件（不含WHERE）</param>
        /// <param name="parms">更新参数</param>
        /// <returns></returns>
        public static int Update(string updateSql, string whereSql, params SqlParameter[] parms)
        {
            return Update(null, updateSql, whereSql, parms);
        }
		
		/// <summary>
        /// 更新一条数据
        /// </summary>
        /// <remarks>
        ///  e.g.:  
        ///  int result = Update("Name = @Name", "ID = @ID", Model);
        /// </remarks>
        /// <param name="updateSql">更新内容(不含SET)</param>
        /// <param name="whereSql">更新条件（不含WHERE）</param>
        /// <param name="info">更新参数</param>
        /// <returns></returns>
		public static int Update(SqlTransaction trans, string updateSql, string whereSql, Info_Model info)
		{		
			SqlParameter[] parms = new SqlParameter[]{
                    new SqlParameter("@title", info.title),    
                    new SqlParameter("@context", info.context),    
                    new SqlParameter("@sort", info.sort),    
					new SqlParameter("@ID", info.ID)
			};
			
            if (trans == null)
                return Update(updateSql, whereSql, parms);
            else
			    return Update(trans, updateSql, whereSql, parms);
		} 
        
        /// <summary>
        /// 更新一条数据
        /// </summary>
        /// <remarks>
        ///  e.g.:  
        ///  int result = Update("Name = @Name", "ID = @ID", Model);
        /// </remarks>
        /// <param name="updateSql">更新内容(不含SET)</param>
        /// <param name="whereSql">更新条件（不含WHERE）</param>
        /// <param name="info">更新参数</param>
        /// <returns></returns>
		public static int Update(string updateSql, string whereSql, Info_Model info)
		{				
			return Update(null, updateSql, whereSql, info);
		} 
		
		/// <summary>
		/// 根据自定义条件删除记录
		/// </summary>
		/// <remarks>
        ///  e.g.:  
        ///  int result = Delete("Name = @Name", new SqlParameter("@Name", "user0"));
        /// </remarks>
		/// <param name="whereSql">Condition</param>
		/// <returns>Delete Number</returns>
		public static int Delete(SqlTransaction trans, string whereSql, params SqlParameter[] parms)
		{
            if (string.IsNullOrEmpty(whereSql)) throw new Exception("删除条件不允许为空，防止误删所有数据");
			string sql = "DELETE FROM [Info] WHERE " + whereSql;
            
            if (trans == null)
			    return BuShi123SqlHelper.ExecuteNonQuery(sql, parms);
            else
                return BuShi123SqlHelper.ExecuteNonQuery(trans, sql, parms);
		}
        
        /// <summary>
		/// 根据自定义条件删除记录
		/// </summary>
		/// <remarks>
        ///  e.g.:  
        ///  int result = Delete("Name = @Name", new SqlParameter("@Name", "user0"));
        /// </remarks>
		/// <param name="whereSql">Condition</param>
		/// <returns>Delete Number</returns>
		public static int Delete(string whereSql, params SqlParameter[] parms)
		{
			return Delete(null, whereSql, parms);
		}
		
		/// <summary>
		/// 根据主键删除记录记录
		/// </summary>
		/// <remarks>
        ///  e.g.:  
        ///  int result = Delete(iD);
        /// </remarks>
		/// <param name="iD">主键</param>
		/// <returns>Delete Number</returns>
		public static int DeleteByKeyField(SqlTransaction trans, int iD)
		{
			string sql = "ID=@ID";
			if (trans == null)
                return Delete(sql, new SqlParameter("@ID", iD));
            else
                return Delete(trans, sql, new SqlParameter("@ID", iD));
		}
		
        /// <summary>
		/// 根据主键删除记录记录
		/// </summary>
		/// <remarks>
        ///  e.g.:  
        ///  int result = Delete(iD);
        /// </remarks>
		/// <param name="iD">主键</param>
		/// <returns>Delete Number</returns>
		public static int DeleteByKeyField(int iD)
		{
			return DeleteByKeyField(null, iD);
		}
        
		/// <summary>
		/// 根据自定义条件查询
		/// </summary>
		/// <remarks>
        ///  e.g.:  
        ///  int result = GetModel("ID=@ID", new SqlParameter("@ID", "123"));
        /// </remarks>
		/// <param name="iD"></param>
		/// <returns>Info对象</returns>
 		public static Info_Model GetModel(string whereSql, params SqlParameter[] parms)
		{			
			string cmdText = "SELECT TOP 1 * FROM [Info] WHERE " + whereSql;
			
			using (IDataReader rdr = BuShi123SqlHelper.ExecuteReader(cmdText, parms))
            {
                if (rdr.Read())
                {
                    return ConvertToModel(rdr);
                }
                else
                {
                    return null;
                }
            }
		}
		
		/// <summary>
		/// 根据主键查询
		/// </summary>
		/// <param name="iD"></param>
		/// <returns>Info对象</returns>
 		public static Info_Model GetModelByKeyField(int iD)
		{			
			string sql = "ID=@ID";
			return GetModel(sql, new SqlParameter("@ID", iD));
		}
        
        /// <summary>
		/// 将DataRow转换为实体
		/// </summary>
		/// <param name="DataRow"></param>
		/// <returns>Info对象</returns>
        public static Info_Model ConvertToModel(DataRow dr)
        {
            Info_Model m = new Info_Model();
            for (int i = 0; i < dr.Table.Columns.Count; i++)
            {
                switch(dr.Table.Columns[i].ColumnName.ToLower())
                {
                    case "id":
                        m.ID =  Convert.ToInt32(dr["ID"]); 
                        break;
                    case "title":
                        m.title =  Convert.ToString(dr["title"]); 
                        break;
                    case "context":
                        m.context =  Convert.ToString(dr["context"]); 
                        break;
                    case "sort":
                        m.sort =  Convert.ToInt32(dr["sort"]); 
                        break;
                }
            }
            return m;
        }
               
		/// <summary>
		/// 将将DataReader转换为实体转换成实体对象
		/// </summary>
		/// <param name="dr"></param>
		/// <returns></returns>
		public static Info_Model ConvertToModel(IDataReader dr)
		{
			Info_Model m = new Info_Model();
			for (int i = 0; i < dr.FieldCount; i++)
			{
			  switch(dr.GetName(i).ToLower())
			  {
                    case "id":
                        m.ID =  Convert.ToInt32(dr["ID"]); 
                        break;
                    case "title":
                        m.title =  Convert.ToString(dr["title"]); 
                        break;
                    case "context":
                        m.context =  Convert.ToString(dr["context"]); 
                        break;
                    case "sort":
                        m.sort =  Convert.ToInt32(dr["sort"]); 
                        break;
			   }
		   }
		   return m;
		}
		
		/// <summary>
		/// 获得实体列表
		/// </summary>
        /// <param name="selectSql">查询字段，为空则查询所有字段</param>
        /// <param name="whereSql">查询条件</param>
        /// <param name="orderSql">排序字段</param>
        /// <param name="parms">参数</param>
		/// <returns>实体集合</returns>
		public static List<Info_Model> GetModelList(string selectSql, string whereSql, string orderSql, params SqlParameter[] parms)
		{
			if (string.IsNullOrEmpty(selectSql))
				selectSql = "*";
				
			if (!string.IsNullOrEmpty(whereSql))
				whereSql = " WHERE " + whereSql;
				
			if (!string.IsNullOrEmpty(orderSql))
				orderSql = " ORDER BY " + orderSql;
				
			string sql = "SELECT " + selectSql + " FROM [Info] " + whereSql + orderSql;
			
			using (IDataReader rdr = BuShi123SqlHelper.ExecuteReader(sql, parms))
            {
                var result = new List<Info_Model>();
                while (rdr.Read())
                {               
                    result.Add(ConvertToModel(rdr));
                }
                return result;
            }
		}
	
	/// <summary>
	/// 获得列表
	/// </summary>
	/// <param name="selectSql">查询字段，为空则查询所有字段</param>
        /// <param name="whereSql">查询条件</param>
        /// <param name="orderSql">排序字段</param>
        /// <param name="parms">参数</param>
	/// <returns></returns>
	public static DataTable GetList(string selectSql, string whereSql, string orderSql, params SqlParameter[] parms)
	{
		if (string.IsNullOrEmpty(selectSql))
			selectSql = "*";
				
		if (!string.IsNullOrEmpty(whereSql))
			whereSql = " WHERE " + whereSql;
				
			if (!string.IsNullOrEmpty(orderSql))
				orderSql = " ORDER BY " + orderSql;
				
			string sql = "SELECT " + selectSql + " FROM [Info] " + whereSql + orderSql;
			
			DataSet ds = BuShi123SqlHelper.ExecuteDataSet(sql, parms);
            if (ds.Tables != null && ds.Tables.Count > 0)
            {
                return ds.Tables[0]; 
            }
            else
            {
                return null;
            }
		}
	
	    /// <summary>
        /// 分页返回符合条件的记录，可指定返回字段
        /// </summary>
        /// <param name="totalCount">返回所有记录数,如果输入大于0,则不重新计算</param>
        /// <param name="conditionSql">指定条件(不含Where)</param>
        /// <param name="selectFields">要返回的字段，为空则返回所有字段</param>
        /// <param name="orderBy">排序方式(不包含order by)</param>
        /// <param name="pageSize">分页大小--每页包含的记录数</param>
        /// <param name="pageIndex">页码--要获取第几页的记录</param>
        /// <param name="parms">执行SQL语句使用的参数</param>
        /// <returns>按分页大小返回指定页码的记录的指定字段</returns>
        public static List<Info_Model> GetModelListByPage(ref int totalCount, string conditionSql, string selectFields, string orderBy, int pageSize, int pageIndex, params SqlParameter[] parms)
        {
            string sql = SqlHelper.GetPageSQL("Info", "ID", conditionSql, selectFields, orderBy, pageSize, pageIndex);
            if (totalCount <= 0) sql += ";select isnull(count(*),0) from [Info] " + ((conditionSql.Trim() == "") ? "" : ("where " + conditionSql));
			DataSet ds = BuShi123SqlHelper.ExecuteDataSet(sql, parms);
			if (totalCount <= 0) totalCount = int.Parse(ds.Tables[1].Rows[0][0].ToString());
			var result = new List<Info_Model>();
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                result.Add(ConvertToModel(dr));
            }
            return result;
        }
	
        /// <summary>
        /// 分页返回符合条件的记录，可指定返回字段
        /// 注意：仅MSSQL2005及以上可用
        /// </summary>
        /// <param name="joinSql">join语句，如inner Join B on GP_Agency.fdAgenID=B.fdAgenID</param>
        /// <param name="keyFiled">唯一主键，用于排序分页</param>
        /// <param name="totalCount">返回所有记录数,如果输入大于0,则不重新计算</param>
        /// <param name="conditionSql">指定条件(不含Where)</param>
        /// <param name="selectFields">要返回的字段，为空则返回所有字段</param>
        /// <param name="orderBy">排序方式(不包含order by)</param>
        /// <param name="pageSize">分页大小--每页包含的记录数</param>
        /// <param name="pageIndex">页码--要获取第几页的记录</param>
        /// <param name="parms">执行SQL语句使用的参数</param>
        /// <returns>按分页大小返回指定页码的记录的指定字段</returns>
        public static DataTable GetListMSSQL2005_JOIN(string joinSql, string keyFiled, string conditionSql, string selectFields, string orderBy, ref int totalCount, int pageSize, int pageIndex, params SqlParameter[] parms)
        {
            if (orderBy.Trim() == "") orderBy = keyFiled;
            string sql = BuShi123SqlHelper.GetPageSQL_MSSQL2005_JOIN("[Info] " + joinSql + "", keyFiled, conditionSql, selectFields, orderBy, pageSize, pageIndex);
            if (totalCount <= 0) sql += ";select isnull(count(*),0) from [Info] " + joinSql + " " + ((conditionSql.Trim() == "") ? "" : ("where " + conditionSql));
            DataSet ds = BuShi123SqlHelper.ExecuteDataSet(sql, parms);
            if (totalCount <= 0) totalCount = int.Parse(ds.Tables[1].Rows[0][0].ToString());
            return ds.Tables[0];
        }

        /// <summary>
        /// 分页返回符合条件的记录，可指定返回字段
        /// 注意：仅MSSQL2005及以上可用
        /// </summary>
        /// <param name="joinSql">join语句，如inner Join B on GP_Agency.fdAgenID=B.fdAgenID</param>
        /// <param name="keyFiled">唯一主键，用于排序分页</param>
        /// <param name="totalCount">返回所有记录数,如果输入大于0,则不重新计算</param>
        /// <param name="conditionSql">指定条件(不含Where)</param>
        /// <param name="selectFields">要返回的字段，为空则返回所有字段</param>
        /// <param name="orderBy">排序方式(不包含order by)</param>
        /// <param name="pageSize">分页大小--每页包含的记录数</param>
        /// <param name="pageIndex">页码--要获取第几页的记录</param>
        /// <param name="parms">执行SQL语句使用的参数</param>
        /// <returns>按分页大小返回指定页码的记录的指定字段</returns>
        public static List<Info_Model> GetModelListMSSQL2005_JOIN(string joinSql, string keyFiled, string conditionSql, string selectFields, string orderBy, ref int totalCount, int pageSize, int pageIndex, params SqlParameter[] parms)
        {
            DataTable dt = GetListMSSQL2005_JOIN(joinSql, keyFiled, conditionSql, selectFields, orderBy, ref totalCount, pageSize, pageIndex, parms);
            List<Info_Model> modelList = new List<Info_Model>();
            foreach (DataRow dr in dt.Rows) 
                modelList.Add(ConvertToModel(dr));
            return modelList;
        }
    
        /// <summary>
        /// 分页返回符合条件的记录，可指定返回字段
        /// </summary>
        /// <param name="totalCount">返回所有记录数,如果输入大于0,则不重新计算</param>
        /// <param name="conditionSql">指定条件(不含Where)</param>
        /// <param name="selectFields">要返回的字段，为空则返回所有字段</param>
        /// <param name="orderBy">排序方式(不包含order by)</param>
        /// <param name="pageSize">分页大小--每页包含的记录数</param>
        /// <param name="pageIndex">页码--要获取第几页的记录</param>
        /// <param name="parms">执行SQL语句使用的参数</param>
        /// <returns>按分页大小返回指定页码的记录的指定字段</returns>
        public static DataTable GetListByPage(ref int totalCount, string conditionSql, string selectFields, string orderBy, int pageSize, int pageIndex, params SqlParameter[] parms)
        {
            string sql = SqlHelper.GetPageSQL("Info", "ID", conditionSql, selectFields, orderBy, pageSize, pageIndex);
            if (totalCount <= 0) sql += ";select isnull(count(*),0) from [Info] " + ((conditionSql.Trim() == "") ? "" : ("where " + conditionSql));
            DataSet ds = BuShi123SqlHelper.ExecuteDataSet(sql, parms);
            if (totalCount <= 0) totalCount = int.Parse(ds.Tables[1].Rows[0][0].ToString());
            return ds.Tables[0];
        }
		
		/// <summary>
        /// 统计符合条件的记录数
        /// </summary>
        /// <param name="conditionSql">条件SQL(不含where)</param>
        /// <param name="parms">SQL参数</param>
        /// <returns></returns>
        public static int GetListCount(string conditionSql, params SqlParameter[] parms)
        {
            return BuShi123SqlHelper.ExecuteScalar("select isnull(count(*),0) from [Info] " + ((conditionSql.Trim() == "") ? "" : ("where " + conditionSql)), parms).ToInt();
        }
	}	
}

